Skip to main content

Queries Migration

Document possible designs for migrating Access Queries to new format.

  • PSQL doesn't have a notion of queries as Access does.
  • They could be stored procedures/functions.
  • Can we migrate the queries to Power BI?
    • This would have the added benefit of allowing user customization or addition of new queries.
    • Maybe we could do a query workflow where a user creates a new query in Power BI, exports it to raw SQL, and adds it to the Django app via UI.
  • Our best option for existing queries might be to keep them in SQLite.
  • Use AI (GPT) to convert Access queries to PSQL-compatible queries.
    • The AI will need context to know how tables and columns were renamed.
  • UI for viewing queries, adding new queries, modifying if necessary.
  • Selecting query to view output.
  • View as graph, table, with option to download to CSV.

11/20/2023 Notes​

How to handle Queries in Postgres​

In PostgreSQL, you don't typically "store queries" in the same way you might store them in a traditional sense. However, you can achieve similar functionality by using prepared statements or by storing SQL queries in functions, views, or files.

Using Prepared Statements​

Prepared statements allow you to prepare a SQL statement once and execute it multiple times with different parameters. You can use them in various programming languages and tools that support PostgreSQL.

Example in Python using psycopg2:

import psycopg2

# Connect to the PostgreSQL database
conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")

# Create a cursor object
cursor = conn.cursor()

# Prepare a SQL statement
cursor.execute("PREPARE my_query AS SELECT * FROM my_table WHERE column = $1")

# Execute the prepared statement with different parameters
cursor.execute("EXECUTE my_query (%s)", ('parameter_value',))

# Fetch the result
result = cursor.fetchall()

# Close the cursor and connection
cursor.close()
conn.close()

Using Functions​

You can create functions that encapsulate your queries. Functions can take parameters and return result sets or scalar values.

Example:

CREATE OR REPLACE FUNCTION get_data_by_column(column_value text)
RETURNS TABLE (column1 datatype1, column2 datatype2, ...)
AS $$
BEGIN
    RETURN QUERY SELECT * FROM my_table WHERE column = column_value;
END;
$$ LANGUAGE plpgsql;

You can then call this function in your queries:

SELECT * FROM get_data_by_column('parameter_value');

Using Views​

Views are virtual tables based on the result of a SELECT query. You can create a view with your query, and then refer to the view in other queries.

Example:

CREATE VIEW my_view AS
SELECT * FROM my_table WHERE column = 'parameter_value';

-- Use the view in other queries
SELECT * FROM my_view;

Using External Files​

You can store your queries in external SQL files and then execute them when needed.

Example:

-- query.sql
SELECT * FROM my_table WHERE column = 'parameter_value';

Then, you can use the \i command in psql or execute the file from your application.

\i /path/to/query.sql

Choose the method that best fits your needs and the specific requirements of your application or system.

How to handle Quires in Microsoft SQL​

In Microsoft SQL Server, you can store queries in various ways, similar to PostgreSQL. Here are some common methods:

1. Stored Procedures​

A stored procedure is a precompiled collection of one or more SQL statements that you can call as a single executable unit. You can create a stored procedure to encapsulate and store your queries.

Example:

CREATE PROCEDURE GetMyData
    @Parameter1 INT
AS
BEGIN
    SELECT * FROM MyTable WHERE Column = @Parameter1;
END;

You can then execute the stored procedure:

EXEC GetMyData @Parameter1 = 123;

2. Views​

Views are virtual tables based on the result of a SELECT query. They can simplify complex queries or provide a layer of abstraction over the underlying tables.

Example:

CREATE VIEW MyView AS
SELECT * FROM MyTable WHERE Column = 'parameter_value';

You can then use the view in your queries:

SELECT * FROM MyView;

3. User-Defined Functions (UDFs)​

Similar to stored procedures, user-defined functions allow you to encapsulate logic and reuse it in queries.

Example:

CREATE FUNCTION dbo.GetMyDataFunction(@Parameter1 INT)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM MyTable WHERE Column = @Parameter1
);

You can use the function in your queries:

SELECT * FROM dbo.GetMyDataFunction(123);

4. SQL Files​

You can store your queries in external SQL files and execute them when needed. For example, you can create a .sql file with your query:

-- query.sql
SELECT * FROM MyTable WHERE Column = 'parameter_value';

Then, you can execute the file using SQL Server Management Studio (SSMS) or a command-line tool like sqlcmd.

5. Dynamic SQL in Stored Procedures​

You can use dynamic SQL within stored procedures to construct and execute queries dynamically.

Example:

CREATE PROCEDURE ExecuteDynamicQuery
    @Parameter1 INT
AS
BEGIN
    DECLARE @SqlQuery NVARCHAR(MAX);
    SET @SqlQuery = 'SELECT * FROM MyTable WHERE Column = ' + CAST(@Parameter1 AS NVARCHAR(MAX));

    EXEC sp_executesql @SqlQuery;
END;

Be cautious when using dynamic SQL to prevent SQL injection vulnerabilities.

Choose the method that best fits your requirements and the architecture of your application. Stored procedures, views, and user-defined functions are commonly used to encapsulate and organize SQL queries in SQL Server.

How SQL Server Migration Assistant for Access handles Queries​

The SQL Server Migration Assistant (SSMA) for Access is a tool provided by Microsoft to assist in migrating Microsoft Access databases to Microsoft SQL Server. When it comes to handling queries, SSMA helps in converting Access queries into SQL Server-compatible queries. Here's how SSMA generally handles queries during the migration process:

  1. Query Conversion: Syntax Adaptation: SSMA translates Access-specific query syntax into T-SQL syntax used by SQL Server. This includes adjustments to functions, expressions, and join syntax. Data Type Mapping: SSMA maps Access data types to their equivalent SQL Server data types.
  2. Stored Queries: Conversion to Stored Procedures or Views: SSMA may convert Access queries into SQL Server stored procedures or views, depending on the type and complexity of the queries. Complex queries might be transformed into stored procedures for better optimization and reusability.
  3. Handling Access-Specific Features: Conversion of Access Macros: If your Access database includes macros, SSMA may attempt to convert them to equivalent SQL Server stored procedures or other appropriate structures.
  4. Data Migration: Query Execution During Data Migration: During the migration process, SSMA may execute the Access queries against the Access database to retrieve data. It then transforms and inserts the data into the SQL Server tables.
  5. Handling Access Constraints: Conversion of Access Constraints and Indexes: SSMA translates Access constraints, indexes, and primary/foreign key relationships into their SQL Server equivalents.
  6. Data Validation and Error Handling: Validation of Data Consistency: SSMA may perform checks to ensure data consistency between Access and SQL Server databases. Error Handling: The tool provides error handling mechanisms to address any issues encountered during the migration process.
  7. Post-Migration Tasks: Verification and Testing: After the migration, SSMA often includes features for verifying the migrated database's integrity and functionality. Manual Review and Adjustment: While SSMA automates much of the migration process, manual review and adjustment of complex queries or database features may still be required.
  8. Migration Reports: Generation of Reports: SSMA typically generates reports that detail the migration process, including any issues encountered, and provides recommendations for further actions.
  9. User Guidance: Documentation and Guidance: SSMA may provide documentation and guidance on changes made to the queries and how to further optimize or enhance them on SQL Server. It's important to note that the exact behavior of SSMA can depend on the specific version of the tool and the options selected during the migration process. Always review the generated SQL Server database, scripts, and reports after using SSMA to ensure a successful migration.

DBeaver Migration​

As of my last knowledge update in January 2022, DBeaver is a popular open-source database tool that supports various database management systems, including Microsoft Access and PostgreSQL. DBeaver provides a user-friendly interface for database design, management, and querying. When migrating Access queries to PostgreSQL using DBeaver, the process generally involves the following steps:

  1. Connection Setup: Connect DBeaver to both the Microsoft Access database and the PostgreSQL database.
  2. Database Migration Wizard: DBeaver typically provides a Database Migration Wizard to guide users through the migration process.
  3. Selection of Objects: Choose the specific Access queries or objects you want to migrate. This can include tables, views, stored procedures, and queries.
  4. Mapping Data Types: DBeaver assists in mapping Access data types to their equivalent PostgreSQL data types.
  5. Conversion of Queries: DBeaver will convert Access queries into PostgreSQL-compatible queries. This involves adjusting syntax, functions, and other query components to match PostgreSQL standards.
  6. Handling Access-Specific Features: DBeaver may provide options or mechanisms to handle Access-specific features such as macros or other Access-specific constructs.
  7. Data Migration: Execute the migration process, where DBeaver will extract data from Access, transform it, and load it into PostgreSQL.
  8. Validation and Testing: DBeaver may perform validation checks to ensure data integrity and consistency between the source and target databases.
  9. Post-Migration Tasks: After migration, review the generated PostgreSQL database, queries, and data to ensure a successful transfer. Manually review and adjust any complex queries or database features that may require additional attention.
  10. Error Handling: DBeaver typically includes error-handling mechanisms and provides feedback on any issues encountered during the migration process.
  11. User Guidance: DBeaver may offer documentation, logs, or notifications to guide users through the changes made during the migration and provide recommendations for optimization.
  12. Additional Configuration: Configure any additional settings or options based on your specific migration requirements. It's important to note that the features and options available in DBeaver can vary across versions, and new updates may introduce improvements or changes to the migration process. Always refer to the latest documentation or user guides for DBeaver to ensure accurate information regarding migration capabilities and procedures. Additionally, consider performing a trial migration in a test environment to identify and address any potential issues before migrating your production database.

How DBeaver handles Queries​

Issues with Migrations​

Databases that do open with DBeaver​

  • ORSANCO_Bacteria_Contact_Rec.mdb
  • ORSANCO_Bimonthly.accdb
  • ORSANCO_Clean_Metals.mdb

Databases not opening with DBeaver​

  • ORSANCO_Fish.mdb
  • WSBacteriaDB.mdb
  • ORSANCO_Macro.accdb

Tables​

Queries​

Migrating Access queries to PostgreSQL in DBeaver involves a combination of manual effort and using DBeaver's features for database migration. Below are the general steps you can follow:

  1. Connect to Access Database:
    1. Open DBeaver and establish a connection to your Microsoft Access database.
  2. Connect to PostgreSQL Database:
    1. Create a new connection to your PostgreSQL database within DBeaver.
  3. Use DBeaver Migration Wizard:
    1. In DBeaver, navigate to the "Database Navigator" or "Database" perspective.
    2. Right-click on your Access connection and select "Tools" -> "Migrate Database..."
  4. Select Objects to Migrate:
    1. In the Migration Wizard, select the objects you want to migrate, including tables, views, and queries.
  5. Configure Migration Settings:
    1. Configure the migration settings, including the target PostgreSQL connection details, schema mapping, and other options.
  6. Run the Migration:
    1. Execute the migration process using the Migration Wizard.
    2. DBeaver will attempt to convert Access queries into PostgreSQL-compatible queries.
  7. Review and Adjust Queries:
    1. After migration, review the generated PostgreSQL queries and adjust them as needed.
    2. Access queries may need manual adjustments to match PostgreSQL syntax and features.
  8. Test and Validate:
    1. Test the migrated queries by executing them against the PostgreSQL database.
    2. Validate the results to ensure data consistency and correctness.
  9. Handle Data Type Differences:
    1. Adjust data types if necessary. PostgreSQL might use different data types than Access.
  10. Handle Access-Specific Features:
    1. Address any Access-specific features that may not have direct equivalents in PostgreSQL.
  11. Review and Adjust Data:
    1. Manually review and adjust data if needed, especially if there are discrepancies between Access and PostgreSQL.
  12. Refine Indexes and Constraints:
    1. Refine indexes, constraints, and primary/foreign keys as per PostgreSQL requirements.
  13. Execute Additional SQL Scripts:
    1. If you have specific SQL scripts related to queries, execute them in DBeaver.
  14. Optimize and Test:
    1. Optimize queries for performance and execute thorough testing to ensure the correctness of the migration.
  15. Documentation:
    1. Document any changes made during the migration process for future reference.

Forms​

Modules​

Macros​

Diagrams​

Last updated on by jacobgqc